using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class ProductDAO : IProductDAO
    {
        #region SQL-String
        private const string SQL_SELECT_PRODUCT = "SELECT * FROM Products";

        private const string SQL_SELECT_PRODUCT_BY_ID = "SELECT * FROM Products WHERE ProductID=@ProductID";

        private const string SQL_SELECT_PRODUCT_BY_CATEGORY_ID = "SELECT * FROM Products WHERE CategoryID=@CategoryID";

        private const string SQL_SELECT_COUNT_ID_BY_CATEGORY_AND_UNIT_IN_STOCK = "SELECT COUNT(ProductID) AS ProductNumber FROM Products WHERE CategoryID=@CategoryID AND UnitsInStock > 0";

        private const string SQL_SELECT_TOP_3_PRODUCT = "SELECT TOP 9 ProductID, ProductName, CategoryID, UnitPrice, Picture, Rating, " + 
                                                                "NEWID() as Random FROM Products WHERE Rating > 4 ORDER BY Random";

        //Select Random product
        private const string SQL_SELECT_RANDOM_PRODUCT = "SELECT TOP 6 ProductID, ProductName, CategoryID, UnitPrice, Picture, Rating, " + 
                                                            "NEWID() as Random FROM Products ORDER BY Random";

        private const string SQL_SELECT_PRODUCT_NAME = "SELECT ProductID, CategoryID, ProductName  FROM Products ORDER BY ProductName";


        #region SQL-Search queries

        private const string SQL_GET_PRODUCT_BY_ALL_CATEGORY_AND_SEARCHKEY = "SELECT * FROM Products WHERE ProductName LIKE @Searchkey";

        private const string SQL_COUNT_PRODUCT_ALL_CATEGORY_AND_SEARCHKEY = "SELECT COUNT(*) FROM Products WHERE ProductName LIKE @Searchkey";

        private const string SQL_GET_PRODUCT_BY_CATEGORY_SEARCHKEY = "SELECT * FROM Products WHERE ProductName LIKE @Searchkey AND CategoryID=@CategoryID ";

        private const string SQL_COUNT_PRODUCT_BY_CATEGORY_SEARCHKEY = "SELECT COUNT(*) FROM Products WHERE ProductName LIKE @Searchkey AND CategoryID=@CategoryID ";

        private const string SQL_GET_PRODUCT_BY_ALL_CATEGORY_AND_PRICE = "SELECT * FROM Products WHERE UnitPrice BETWEEN @pricefrom AND @Priceto";

        private const string SQL_COUNT_PRODUCT_ALL_CATEGORY_AND_PRICE = "SELECT COUNT(*) FROM Products WHERE UnitPrice BETWEEN @Pricefrom AND @Priceto";

        private const string SQL_GET_PRODUCT_BY_CATEGORY_AND_PRICE = "SELECT * FROM Products WHERE UnitPrice BETWEEN @Pricefrom AND @Priceto " +
                                "AND CategoryID=@CategoryID";

        private const string SQL_COUNT_PRODUCT_BY_CATEGORY_AND_PRICE = "SELECT COUNT(*) FROM Products WHERE UnitPrice BETWEEN @Pricefrom " +
                                "AND @Priceto AND CategoryID=@CategoryID";

        private const string SQL_GET_PRODUCT_BY_ALL_CATEGORY_AND_SEARCHKEY_AND_PRICE = "SELECT * FROM Products WHERE ProductName LIKE @Searchkey " +
                                "AND UnitPrice BETWEEN @Pricefrom AND @Priceto";

        private const string SQL_COUNT_PRODUCT_BY_ALL_CATEGORY_AND_SEARCHKEY_AND_PRICE = "SELECT COUNT(*) FROM Products WHERE ProductName LIKE @Searchkey " +
                                "AND UnitPrice BETWEEN @Pricefrom AND @Priceto";

        private const string SQL_GET_PRODUCT_BY_CATEGORY_AND_SEARCHKEY_AND_PRICE = "SELECT * FROM Products WHERE ProductName LIKE @Searchkey " +
                                "AND UnitPrice BETWEEN @Pricefrom AND @Priceto AND CategoryID=@CategoryID";

        private const string SQL_COUNT_PRODUCT_BY_CATEGORY_AND_SEARCHKEY_AND_PRICE = "SELECT COUNT(*) FROM Products WHERE ProductName LIKE @Searchkey " +
                               "AND UnitPrice BETWEEN @Pricefrom AND @Priceto AND CategoryID=@CategoryID";
        #endregion

        //Insert SQL-QueryString
        private const string SQL_INSERT_PRODUCT = "INSERT INTO Products " +
                        "(CategoryID, ProductName, UnitPrice, UnitsInStock, UnitsOnOrder, Features, DescriptionBrief, Description, Picture, Rating) " +
                        "VALUES (@CategoryID, @ProductName, @UnitPrice, @UnitsInStock, 0, @Features, @DescriptionBrief, @Description, @Picture, 0)";
        
        //Update SQL-QueryString
        private const string SQL_UPDATE_PRODUCT = "UPDATE Products SET " +
                        "CategoryID=@CategoryID, ProductName=@ProductName, UnitPrice=@UnitPrice, UnitsInStock=@UnitsInStock, Features=@Features, " +
                        "DescriptionBrief=@DescriptionBrief, Description=@Description, Picture=@Picture " +
                        "WHERE ProductID=@ProductID";

        private const string SQL_UPDATE_PICTURE = "UPDATE Products SET Picture=@Picture WHERE ProductID=@ProductID";

        private const string SQL_UPDATE_PRODUCT_RATING = "UPDATE Products SET Rating=(SELECT AVG(ReviewRating) FROM Review WHERE ProductID=@ProductID) " +
                                                            "WHERE ProductID=@ProductID";

        //private const string SQL_UPDATE_PRODUCT_RATING = "UPDATE Products SET Rating = (SELECT CONVERT(VARCHAR(50),(SELECT AVG(ReviewRating) FROM Review WHERE ProductID=@ProductID)))" +
        //                                                    " WHERE ProductID=@ProductID";       

        private const string SQL_UPDATE_PRODUCT_UNIT_ON_ORDER = "UPDATE Products SET UnitsInStock=UnitsInStock-@UnitsOnOrder, UnitsOnOrder=UnitsOnOrder+@UnitsOnOrder where ProductID=@ProductID";

        private const string PROCEDURE_DELETE_PRODUCT = "sp_DeleteProduct";

        private const string PARAM_CATEGORY_ID = "@CategoryID";
        private const string PARAM_PRODUCT_NAME = "@ProductName";
        private const string PARAM_UNIT_PRICE = "@UnitPrice";
        private const string PARAM_UNIT_IN_STOCK = "@UnitsInStock";
        private const string PARAM_FEATURES = "@Features";
        private const string PARAM_DESCRIPTION_BRIEF = "@DescriptionBrief";
        private const string PARAM_DESCRIPTION = "@Description";
        private const string PARAM_PICTURE = "@Picture";
        private const string PARAM_UNIT_ON_ORDER = "@UnitsOnOrder";
        private const string PARAM_RATING = "@Rating";
        private const string PARAM_PRODUCT_ID = "@ProductID";
        private const string PARAM_SEARCHKEY = "@Searchkey";
        private const string PARAM_PRICE_FROM = "@Pricefrom";
        private const string PARAM_PRICE_TO = "@Priceto";

        #endregion

        #region just in case T_T
        private int RoundRating(double rating)
        {
            if (rating > 0 && rating <= 1)
            { 
                return 1; 
            }
            else
            {
                if (rating > 1 && rating <= 2)
                { 
                    return 2;
                }
                else
                {
                    if (rating > 2 && rating <= 3)
                    { 
                        return 3;
                    }
                    else
                    {
                        if (rating > 3 && rating <= 4)
                        { 
                            return 4;
                        }
                        else
                        {
                            if (rating > 4 && rating <= 5)
                            { 
                                return 5;
                            }
                            else
                            { 
                                return 0;
                            }
                        }
                    }
                }
            }
        }
        #endregion

        #region IProductDAO Members

        #region bool Delete(ProductInfo product)
        public bool Delete(ProductInfo product)
        {
            SqlParameter[] parm = new SqlParameter[] { new SqlParameter(PARAM_PRODUCT_ID, SqlDbType.Int) };
            parm[0].Value = product.ProductID;
            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.StoredProcedure, PROCEDURE_DELETE_PRODUCT, parm);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region int CountProduct(string categoryID)
        public int CountProduct(string categoryID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int) };
            if (string.IsNullOrEmpty(categoryID))
                parms[0].Value = 0;
            else
                parms[0].Value = int.Parse(categoryID);

            ProductInfo pro = new ProductInfo();
            object ob = SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_COUNT_ID_BY_CATEGORY_AND_UNIT_IN_STOCK, parms);
            return Int32.Parse(ob.ToString());
        }
        #endregion

        #region IList<ProductInfo> GetAll()
        public IList<ProductInfo> GetAll()
        {
            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_PRODUCT, null))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    pro.UnitsInStock = reader.GetInt16(4);
                    if(!reader.IsDBNull(5))
                        pro.UnitsOnOrder = reader.GetInt16(5);
                    pro.Features = reader.GetString(6);
                    if (!reader.IsDBNull(7))
                        pro.DescriptionBrief = reader.GetString(7);
                    if (!reader.IsDBNull(8))
                        pro.Description = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = RoundRating(reader.GetDouble(10));
                    }
                    pro.Review = new ReviewDAO().GetByProductID(pro.ProductID.ToString());
                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region IList<ProductInfo> GetTop3Product()
        public IList<ProductInfo> GetTop3Product()
        {
            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_TOP_3_PRODUCT, null))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.ProductName = reader.GetString(1);
                    pro.CategoryID = reader.GetInt32(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(4))
                        pro.Picture = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                    {
                        pro.Rating = RoundRating(reader.GetDouble(5));
                        pro.RatingImage = SQLDBHelper.GetStar((int)pro.Rating);
                    }
                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region IList<ProductInfo> GetRandom6Product()
        public IList<ProductInfo> GetRandom6Product()
        {
            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_RANDOM_PRODUCT, null))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.ProductName = reader.GetString(1);
                    pro.CategoryID = reader.GetInt32(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(4))
                        pro.Picture = reader.GetString(4);
                    if (!reader.IsDBNull(5))
                    {
                        pro.Rating = RoundRating(reader.GetDouble(5));
                        pro.RatingImage = SQLDBHelper.GetStar((int)pro.Rating);
                    }
                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region public ProductInfo GetOnlyName()
        public IList<ProductInfo> GetOnlyName()
        {
            IList<ProductInfo> pros = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_PRODUCT_NAME, null))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pros.Add(pro);
                }
            }
            return pros;
        }
        #endregion

        #region ProductInfo GetByID(string productID)
        public ProductInfo GetByID(string productID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_PRODUCT_ID, SqlDbType.Int) };
            if (string.IsNullOrEmpty(productID))
            {
                parms[0].Value = 0;
            }
            else
            {
                parms[0].Value = int.Parse(productID);
            }

            ProductInfo pro = new ProductInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_PRODUCT_BY_ID, parms))
            {
                while (reader.Read())
                {                    
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    pro.UnitsInStock = reader.GetInt16(4);
                    if (!reader.IsDBNull(5))
                        pro.UnitsOnOrder = reader.GetInt16(5);
                    pro.Features = reader.GetString(6);
                    if (!reader.IsDBNull(7))
                        pro.DescriptionBrief = reader.GetString(7);
                    if (!reader.IsDBNull(8))
                        pro.Description = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);

                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = RoundRating(reader.GetDouble(10));
                    }
                    pro.Review = new ReviewDAO().GetByProductID(pro.ProductID.ToString());
                }
            }
            return pro;
        }
        #endregion

        #region IList<ProductInfo> GetByCategoryID(string categoryID)
        public IList<ProductInfo> GetByCategoryID(string categoryID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int) };
            if (string.IsNullOrEmpty(categoryID))
            {
                parms[0].Value = 0;
            }
            else
            {
                parms[0].Value = int.Parse(categoryID);
            }

            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_PRODUCT_BY_CATEGORY_ID, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    pro.UnitsInStock = reader.GetInt16(4);
                    if (!reader.IsDBNull(5))
                        pro.UnitsOnOrder = reader.GetInt16(5);
                    pro.Features = reader.GetString(6);
                    if (!reader.IsDBNull(7))
                        pro.DescriptionBrief = reader.GetString(7);
                    if (!reader.IsDBNull(8))
                        pro.Description = reader.GetString(8);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = RoundRating(reader.GetDouble(10));
                    }
                    pro.RatingImage = SQLDBHelper.GetStar((int)pro.Rating);
                    pro.Review = new ReviewDAO().GetByProductID(pro.ProductID.ToString());
                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region bool Insert(ProductInfo product)
        public bool Insert(ProductInfo product)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRODUCT_NAME, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_UNIT_PRICE, SqlDbType.Decimal),
                                            new SqlParameter(PARAM_UNIT_IN_STOCK, SqlDbType.SmallInt),
                                            new SqlParameter(PARAM_FEATURES, SqlDbType.NText),
                                            new SqlParameter(PARAM_DESCRIPTION_BRIEF, SqlDbType.NText),
                                            new SqlParameter(PARAM_DESCRIPTION, SqlDbType.NText),
                                            new SqlParameter(PARAM_PICTURE, SqlDbType.NVarChar), };
            
            parms[0].Value = product.CategoryID;
            parms[1].Value = product.ProductName;
            parms[2].Value = product.UnitPrice;
            parms[3].Value = product.UnitsInStock;
            parms[4].Value = product.Features;
            parms[5].Value = product.DescriptionBrief;
            parms[6].Value = product.Description;
            parms[7].Value = product.Picture;

            try
            {
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);                
                int result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_PRODUCT, parms);
                SQLDBHelper.SetNewIdentity(conn, product.ProductID);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region bool Update(ProductInfo product)
        public bool Update(ProductInfo product)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRODUCT_NAME, SqlDbType.NVarChar),
                                            new SqlParameter(PARAM_UNIT_PRICE, SqlDbType.Decimal),
                                            new SqlParameter(PARAM_UNIT_IN_STOCK, SqlDbType.SmallInt),
                                            new SqlParameter(PARAM_FEATURES, SqlDbType.NText),
                                            new SqlParameter(PARAM_DESCRIPTION_BRIEF, SqlDbType.NText),
                                            new SqlParameter(PARAM_DESCRIPTION, SqlDbType.NText),
                                            new SqlParameter(PARAM_PICTURE, SqlDbType.NVarChar), 
                                            new SqlParameter(PARAM_PRODUCT_ID, SqlDbType.Int) };

            parms[0].Value = product.CategoryID;
            parms[1].Value = product.ProductName;
            parms[2].Value = product.UnitPrice;
            parms[3].Value = product.UnitsInStock;
            parms[4].Value = product.Features;
            parms[5].Value = product.DescriptionBrief;
            parms[6].Value = product.Description;
            parms[7].Value = product.Picture;
            parms[8].Value = product.ProductID;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_PRODUCT, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region bool UpdatePicture(string imagePath, string productID)
        public bool UpdatePicture(string imagePath, string productID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_PICTURE, SqlDbType.NVarChar), new SqlParameter(PARAM_PRODUCT_ID, SqlDbType.Int) };
            parms[0].Value = imagePath;
            parms[1].Value = int.Parse(productID);

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_PICTURE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region bool UpdateUnitOnOrder(string unitOnOrder, string productID)
        public bool UpdateUnitOnOrder(string unitOnOrder, string productID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_UNIT_ON_ORDER, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRODUCT_ID, SqlDbType.Int) };
            if (!string.IsNullOrEmpty(unitOnOrder))
            {
                parms[0].Value = int.Parse(unitOnOrder);
            }
            if (!string.IsNullOrEmpty(productID))
            {
                parms[1].Value = int.Parse(productID);
            }            
            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_PRODUCT_UNIT_ON_ORDER, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region bool UpdateRating(string productID)
        public bool UpdateRating(string productID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARAM_PRODUCT_ID, SqlDbType.Int) };
            if (!string.IsNullOrEmpty(productID))
            {
                parms[0].Value = int.Parse(productID);
            }
            else
            {
                parms[0].Value = 0;
            }

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_PRODUCT_RATING, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

        #region IList<ProductInfo> GetProductByAllCategoryAndSearchkey(string searchkey)
        public IList<ProductInfo> GetProductByAllCategoryAndSearchkey(string searchkey)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar) };
            parms[0].Value = "%" + searchkey + "%";

            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_GET_PRODUCT_BY_ALL_CATEGORY_AND_SEARCHKEY, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = (float)reader.GetDouble(10);
                    }

                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region string GetCountProductByAllCategoryAndSearchkey(string searchkey)
        public string GetCountProductByAllCategoryAndSearchkey(string searchkey)
        {
            string result = "";
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar) };
            parms[0].Value = "%" + searchkey + "%";

            try
            {
                int i = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_COUNT_PRODUCT_ALL_CATEGORY_AND_SEARCHKEY, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region IList<ProductInfo> GetProductByAllCategoryAndPrice(string pricefrom,string priceto)
        public IList<ProductInfo> GetProductByAllCategoryAndPrice(string pricefrom, string priceto)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int) };

            parms[0].Value = int.Parse(pricefrom);
            parms[1].Value = int.Parse(priceto);


            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_GET_PRODUCT_BY_ALL_CATEGORY_AND_PRICE, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = (float)reader.GetDouble(10);
                    }
                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region string GetCountProductByAllCategoryAndPrice(string pricefrom, string priceto)
        public string GetCountProductByAllCategoryAndPrice(string pricefrom, string priceto)
        {
            string result = "";
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int) };

            parms[0].Value = int.Parse(pricefrom);
            parms[1].Value = int.Parse(priceto);

            try
            {
                int i = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_COUNT_PRODUCT_ALL_CATEGORY_AND_PRICE, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region IList<ProductInfo> GetProductByCategoryAndPrice(string pricefrom,string priceto, int categoryID)
        public IList<ProductInfo> GetProductByCategoryAndPrice(string pricefrom, string priceto, int categoryID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int),
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = int.Parse(pricefrom);
            parms[1].Value = int.Parse(priceto);
            parms[2].Value = categoryID;


            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_GET_PRODUCT_BY_CATEGORY_AND_PRICE, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = (float)reader.GetDouble(10);
                    }

                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region string GetCountProductByCategoryAndPrice(string pricefrom, string priceto,int categoryID)
        public string GetCountProductByCategoryAndPrice(string pricefrom, string priceto, int categoryID)
        {
            string result = "";
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int),
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = int.Parse(pricefrom);
            parms[1].Value = int.Parse(priceto);
            parms[2].Value = categoryID;

            try
            {
                int i = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_COUNT_PRODUCT_BY_CATEGORY_AND_PRICE, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region IList<ProductInfo> GetProductByCategoryAndSearchkey(string searchkey, int categoryID)
        public IList<ProductInfo> GetProductByCategoryAndSearchkey(string searchkey, int categoryID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar),
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = "%" + searchkey + "%";
            parms[1].Value = categoryID;


            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_GET_PRODUCT_BY_CATEGORY_SEARCHKEY, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = (float)reader.GetDouble(10);
                    }

                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region string GetCountProductByCategoryAndSearchkey(string searchkey, int categoryID)
        public string GetCountProductByCategoryAndSearchkey(string searchkey, int categoryID)
        {
            string result = "";
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar),
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = "%" + searchkey + "%";
            parms[1].Value = categoryID;

            try
            {
                int i = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_COUNT_PRODUCT_BY_CATEGORY_SEARCHKEY, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region IList<ProductInfo> GetProductByAllCategoryAndPriceAndSearchkey(string searchkey, string pricefrom, string priceto)
        public IList<ProductInfo> GetProductByAllCategoryAndPriceAndSearchkey(string searchkey, string pricefrom, string priceto)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar),
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int)};

            parms[0].Value = "%" + searchkey + "%";
            parms[1].Value = int.Parse(pricefrom);
            parms[2].Value = int.Parse(priceto);



            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_GET_PRODUCT_BY_ALL_CATEGORY_AND_SEARCHKEY_AND_PRICE, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = (float)reader.GetDouble(10);
                    }
                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region string GetCountProductByAllCategoryAndSearchkey(string searchkey, string pricefrom, string priceto)
        public string GetCountProductByAllCategoryAndPriceAndSearchkey(string searchkey, string pricefrom, string priceto)
        {
            string result = "";
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar),
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int)};

            parms[0].Value = "%" + searchkey + "%";
            parms[1].Value = int.Parse(pricefrom);
            parms[2].Value = int.Parse(priceto);

            try
            {
                int i = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_COUNT_PRODUCT_BY_ALL_CATEGORY_AND_SEARCHKEY_AND_PRICE, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region IList<ProductInfo> GetProductByCategoryAndPriceAndSearchkey(string searchkey, string pricefrom, string priceto, int categoryID)
        public IList<ProductInfo> GetProductByCategoryAndPriceAndSearchkey(string searchkey, string pricefrom, string priceto, int categoryID)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar),
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int),
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = "%" + searchkey + "%";
            parms[1].Value = int.Parse(pricefrom);
            parms[2].Value = int.Parse(priceto);
            parms[3].Value = categoryID;


            IList<ProductInfo> products = new List<ProductInfo>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_GET_PRODUCT_BY_CATEGORY_AND_SEARCHKEY_AND_PRICE, parms))
            {
                while (reader.Read())
                {
                    ProductInfo pro = new ProductInfo();
                    pro.ProductID = reader.GetInt32(0);
                    pro.CategoryID = reader.GetInt32(1);
                    pro.ProductName = reader.GetString(2);
                    pro.UnitPrice = reader.GetDecimal(3);
                    if (!reader.IsDBNull(9))
                    {
                        pro.Picture = reader.GetString(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        pro.Rating = (float)reader.GetDouble(10);
                    }

                    products.Add(pro);
                }
            }
            return products;
        }
        #endregion

        #region string GetCountProductByCategoryAndSearchkey(string searchkey, string pricefrom, string priceto, int categoryID)
        public string GetCountProductByCategoryAndPriceAndSearchkey(string searchkey, string pricefrom, string priceto, int categoryID)
        {
            string result = "";
            SqlParameter[] parms;
            parms = new SqlParameter[] {
                                            new SqlParameter(PARAM_SEARCHKEY, SqlDbType.VarChar),
                                            new SqlParameter(PARAM_PRICE_FROM, SqlDbType.Int),
                                            new SqlParameter(PARAM_PRICE_TO, SqlDbType.Int),
                                            new SqlParameter(PARAM_CATEGORY_ID, SqlDbType.Int)};

            parms[0].Value = "%" + searchkey + "%";
            parms[1].Value = int.Parse(pricefrom);
            parms[2].Value = int.Parse(priceto);
            parms[3].Value = categoryID;

            try
            {
                int i = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_COUNT_PRODUCT_BY_CATEGORY_AND_SEARCHKEY_AND_PRICE, parms);
                result = i.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #endregion
    }
}
